﻿CREATE VIEW dbo.ViewSearchProductUnit
AS
SELECT        dbo.PRODUCT_UNIT.ID AS PRODUCT_UNIT_ID, dbo.PRODUCT_UNIT.TYPE AS PRODUCT_UNIT_TYPE, dbo.PRODUCT_UNIT.NSI_PRODUCT_ID, 
                         dbo.PRODUCT_UNIT.NAME AS PRODUCT_UNIT_NAME, dbo.PRODUCT_UNIT.PID AS PRODUCT_UNIT_PID, dbo.NSI_METER.SHORT_NAME AS METER_NAME, 
                         dbo.NSI_METER.ID AS METER_ID, dbo.PRODUCT_UNIT.CP_STAGE_ID, dbo.PRODUCT_UNIT.VAT_INVOICE_ID, dbo.PRODUCT_UNIT.DIRECT_NUMBER, 
                         COUNT(dbo.PRODUCT_UNIT.ID) AS COUNT, dbo.PRODUCT_UNIT.GUILD_BILL_NUMBER
FROM            dbo.NSI_METER RIGHT OUTER JOIN
                         dbo.NSI_PRODUCT ON dbo.NSI_METER.ID = dbo.NSI_PRODUCT.NSI_METER_ID RIGHT OUTER JOIN
                         dbo.PRODUCT_UNIT ON dbo.NSI_PRODUCT.ID = dbo.PRODUCT_UNIT.NSI_PRODUCT_ID
GROUP BY dbo.PRODUCT_UNIT.ID, dbo.PRODUCT_UNIT.TYPE, dbo.PRODUCT_UNIT.NSI_PRODUCT_ID, dbo.PRODUCT_UNIT.NAME, dbo.PRODUCT_UNIT.PID, 
                         dbo.NSI_METER.SHORT_NAME, dbo.NSI_METER.ID, dbo.PRODUCT_UNIT.CP_STAGE_ID, dbo.PRODUCT_UNIT.VAT_INVOICE_ID, 
                         dbo.PRODUCT_UNIT.DIRECT_NUMBER, dbo.PRODUCT_UNIT.GUILD_BILL_NUMBER